Load packages

library(tidyverse)
library(lubridate)
library(hms)

Attaching package: ‘hms’

The following object is masked from ‘package:lubridate’:

    hms
library(googlesheets4)

#gs4_deauth()
#ppl_15mins <- read_sheet("https://docs.google.com/spreadsheets/d/1uv5SBYklQ-bArCnWPVG1hOvKu3fpgD1sgU8bTZ8sR5o/edit?usp=sharing")
#add column types


col_datatypes <- c('numeric','numeric','date','text',rep('numeric',99))

#?read_excel

hourly1 <- read_excel("Hourly Usage 220326 to 220624.xlsx", col_types = col_datatypes)
Warning: Expecting numeric in A277 / R277C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'
hourly2 <- read_excel("Hourly Usage 220625 to 230623.xlsx", col_types = col_datatypes)
Warning: Expecting numeric in A1096 / R1096C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'
hourly3 <- read_excel("Hourly Usage 230624 to 231121.xlsx", col_types = col_datatypes)
Warning: Expecting numeric in A457 / R457C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'
hourly1
hourly2
hourly3

(ppl_15mins <- bind_rows(hourly1,list(hourly2,hourly3)))
NA
NA
hourly_ppl_pivot <- ppl_15mins %>% 
  rename(date = Date) %>% 
  pivot_longer(!c("Account Number", "Meter Number", date, "Read Type", Min, Max, Total), names_to = "time", values_to = "kWh") 

#rm(hourly_pivot)

hourly_ppl_pivot <- hourly_ppl_pivot %>% 
  mutate(time = parse_time(time, '%H:%M %p'), month = month(date, label=TRUE), year = year(date), yday = yday(date), wday = wday(date, label=TRUE))

(hourly_ppl_net <- hourly_ppl_pivot %>% 
  filter(`Read Type` == "kWh Net"))

Import solar production from another notebook

ggplot(hourly_production, aes(datetime, energy_produced_Wh)) +
  geom_point()


ggplot(hourly_production, aes(time, energy_produced_Wh)) +
  theme(axis.text.x = element_text(angle = 90)) +
  geom_point()

ggplot(hourly_production, aes(datetime, energy_produced_Wh)) +
  geom_point()


ggplot(hourly_production, aes(time, energy_produced_Wh)) +
  theme(axis.text.x = element_text(angle = 90)) +
  geom_point()

Net + Produced = Consumedggplot(hourly_production, aes(datetime, energy_produced_Wh)) +

geom_point()

ggplot(hourly_production, aes(time, energy_produced_Wh)) +

theme(axis.text.x = element_text(angle = 90)) +

geom_point()

hourly_ppl_net %>% arrange(desc(date))

hourly_production %>% arrange(desc(date))
ggplot(hourly_electricity, aes(x=time)) +
  geom_point(aes(y=consumed_kWh,color="red")) 


ggplot(hourly_electricity, aes(x=datetime)) +
  geom_point(aes(y=consumed_kWh,color="red")) 

Bring in Enphase energy production

Bring in PPL net energy usage

Calculate consumption

LS0tCnRpdGxlOiAiSG91cmx5IEVsZWN0cmljaXR5IENvbnN1bXB0aW9uIgpvdXRwdXQ6IGh0bWxfbm90ZWJvb2sKLS0tCgpMb2FkIHBhY2thZ2VzCmBgYHtyfQpsaWJyYXJ5KHRpZHl2ZXJzZSkKbGlicmFyeShsdWJyaWRhdGUpCmxpYnJhcnkoaG1zKQpsaWJyYXJ5KGdvb2dsZXNoZWV0czQpCmBgYAoKYGBge3J9CgojZ3M0X2RlYXV0aCgpCiNwcGxfMTVtaW5zIDwtIHJlYWRfc2hlZXQoImh0dHBzOi8vZG9jcy5nb29nbGUuY29tL3NwcmVhZHNoZWV0cy9kLzF1djVTQllrbFEtYkFyQ25XUFZHMWhPdkt1M2ZwZ0Qxc2dVOGJUWjhzUjVvL2VkaXQ/dXNwPXNoYXJpbmciKQojYWRkIGNvbHVtbiB0eXBlcwoKCmNvbF9kYXRhdHlwZXMgPC0gYygnbnVtZXJpYycsJ251bWVyaWMnLCdkYXRlJywndGV4dCcscmVwKCdudW1lcmljJyw5OSkpCgojP3JlYWRfZXhjZWwKCmhvdXJseTEgPC0gcmVhZF9leGNlbCgiSG91cmx5IFVzYWdlIDIyMDMyNiB0byAyMjA2MjQueGxzeCIsIGNvbF90eXBlcyA9IGNvbF9kYXRhdHlwZXMpCmhvdXJseTIgPC0gcmVhZF9leGNlbCgiSG91cmx5IFVzYWdlIDIyMDYyNSB0byAyMzA2MjMueGxzeCIsIGNvbF90eXBlcyA9IGNvbF9kYXRhdHlwZXMpCmhvdXJseTMgPC0gcmVhZF9leGNlbCgiSG91cmx5IFVzYWdlIDIzMDYyNCB0byAyMzExMjEueGxzeCIsIGNvbF90eXBlcyA9IGNvbF9kYXRhdHlwZXMpCgpob3VybHkxCmhvdXJseTIKaG91cmx5MwoKKHBwbF8xNW1pbnMgPC0gYmluZF9yb3dzKGhvdXJseTEsbGlzdChob3VybHkyLGhvdXJseTMpKSkKCgpgYGAKCmBgYHtyfQpob3VybHlfcHBsX3Bpdm90IDwtIHBwbF8xNW1pbnMgJT4lIAogIHJlbmFtZShkYXRlID0gRGF0ZSkgJT4lIAogIHBpdm90X2xvbmdlcighYygiQWNjb3VudCBOdW1iZXIiLCAiTWV0ZXIgTnVtYmVyIiwgZGF0ZSwgIlJlYWQgVHlwZSIsIE1pbiwgTWF4LCBUb3RhbCksIG5hbWVzX3RvID0gInRpbWUiLCB2YWx1ZXNfdG8gPSAia1doIikgCgojcm0oaG91cmx5X3Bpdm90KQoKaG91cmx5X3BwbF9waXZvdCA8LSBob3VybHlfcHBsX3Bpdm90ICU+JSAKICBtdXRhdGUodGltZSA9IHBhcnNlX3RpbWUodGltZSwgJyVIOiVNICVwJyksIG1vbnRoID0gbW9udGgoZGF0ZSwgbGFiZWw9VFJVRSksIHllYXIgPSB5ZWFyKGRhdGUpLCB5ZGF5ID0geWRheShkYXRlKSwgd2RheSA9IHdkYXkoZGF0ZSwgbGFiZWw9VFJVRSkpCgooaG91cmx5X3BwbF9uZXQgPC0gaG91cmx5X3BwbF9waXZvdCAlPiUgCiAgZmlsdGVyKGBSZWFkIFR5cGVgID09ICJrV2ggTmV0IikpCmBgYAoKSW1wb3J0IHNvbGFyIHByb2R1Y3Rpb24gZnJvbSBhbm90aGVyIG5vdGVib29rCgpgYGB7cn0KZ2dwbG90KGhvdXJseV9wcm9kdWN0aW9uLCBhZXMoZGF0ZXRpbWUsIGVuZXJneV9wcm9kdWNlZF9XaCkpICsKICBnZW9tX3BvaW50KCkKCmdncGxvdChob3VybHlfcHJvZHVjdGlvbiwgYWVzKHRpbWUsIGVuZXJneV9wcm9kdWNlZF9XaCkpICsKICB0aGVtZShheGlzLnRleHQueCA9IGVsZW1lbnRfdGV4dChhbmdsZSA9IDkwKSkgKwogIGdlb21fcG9pbnQoKQpgYGAKCmBgYHtyfQpnZ3Bsb3QoaG91cmx5X3Byb2R1Y3Rpb24sIGFlcyhkYXRldGltZSwgZW5lcmd5X3Byb2R1Y2VkX1doKSkgKwogIGdlb21fcG9pbnQoKQoKZ2dwbG90KGhvdXJseV9wcm9kdWN0aW9uLCBhZXModGltZSwgZW5lcmd5X3Byb2R1Y2VkX1doKSkgKwogIHRoZW1lKGF4aXMudGV4dC54ID0gZWxlbWVudF90ZXh0KGFuZ2xlID0gOTApKSArCiAgZ2VvbV9wb2ludCgpCmBgYAoKTmV0ICsgUHJvZHVjZWQgPSBDb25zdW1lZGdncGxvdChob3VybHlfcHJvZHVjdGlvbiwgYWVzKGRhdGV0aW1lLCBlbmVyZ3lfcHJvZHVjZWRfV2gpKSArCgogIGdlb21fcG9pbnQoKQoKZ2dwbG90KGhvdXJseV9wcm9kdWN0aW9uLCBhZXModGltZSwgZW5lcmd5X3Byb2R1Y2VkX1doKSkgKwoKICB0aGVtZShheGlzLnRleHQueCA9IGVsZW1lbnRfdGV4dChhbmdsZSA9IDkwKSkgKwoKICBnZW9tX3BvaW50KCkKYGBge3J9CgojIGhvdXJseV9wcGxfbmV0IDwtIGhvdXJseV9wcGxfbmV0ICU+JSBtdXRhdGUoZGF0ZSA9IGFzX2RhdGUoZGF0ZSkpCgojIGhvdXJseV9wcGxfbmV0ICU+JSBhcnJhbmdlKGRlc2MoZGF0ZSkpCiMgaG91cmx5X3Byb2R1Y3Rpb24gJT4lIGFycmFuZ2UoZGVzYyhkYXRlKSkKCgooaG91cmx5X2VsZWN0cmljaXR5IDwtIGhvdXJseV9wcGxfbmV0ICU+JSAKICAgIGlubmVyX2pvaW4oaG91cmx5X3Byb2R1Y3Rpb24sIGJ5ID0gam9pbl9ieShkYXRlLHRpbWUpKSAgJT4lIAogICAgbXV0YXRlKGNvbnN1bWVkX2tXaCA9IGtXaCArIGVuZXJneV9wcm9kdWNlZF9XaC8xMDAwLCBwcm9kdWNlZF9rV2ggPSBlbmVyZ3lfcHJvZHVjZWRfV2gvMTAwMCkgICU+JSAKICAgIHJlbmFtZShuZXRfa1doID0ga1doKSAlPiUgCiAgICBzZWxlY3QoZGF0ZXRpbWUsIGRhdGUsIHRpbWUsIG5ldF9rV2gsIHByb2R1Y2VkX2tXaCwgY29uc3VtZWRfa1doKSkKCmBgYAoKYGBge3J9CmdncGxvdChob3VybHlfZWxlY3RyaWNpdHksIGFlcyh4PXRpbWUpKSArCiAgZ2VvbV9wb2ludChhZXMoeT1jb25zdW1lZF9rV2gsY29sb3I9InJlZCIpKSAKCmdncGxvdChob3VybHlfZWxlY3RyaWNpdHksIGFlcyh4PWRhdGV0aW1lKSkgKwogIGdlb21fcG9pbnQoYWVzKHk9Y29uc3VtZWRfa1doLGNvbG9yPSJyZWQiKSkgCmBgYAoKCgoKQnJpbmcgaW4gRW5waGFzZSBlbmVyZ3kgcHJvZHVjdGlvbgpgYGB7cn0KCmBgYAoKQnJpbmcgaW4gUFBMIG5ldCBlbmVyZ3kgdXNhZ2UKYGBge3J9CgpgYGAKCkNhbGN1bGF0ZSBjb25zdW1wdGlvbgpgYGB7cn0KCmBgYAoKCgo=